from tkinter import ttk
from tkinter import *
import pymysql
from tkinter.messagebox import *


def connects():  # 打开sql连接
    conn_01 = pymysql.connect(host="127.0.0.1", port=3306, user="root", password="123456", db="mytest", charset="utf8")
    return conn_01


def exits():  # 关闭sql连接并退出程序
    conn = connects()
    conn.close()
    win.destroy()


def values():
    conn = connects()
    cursor = conn.cursor()
    sql = f"select cname from course"
    cursor.execute(sql)
    date = cursor.fetchall()
    conn.commit()
    cursor.close()
    return date


def commits():  # 提交
    try:
        name = u_var.get()
        pwd = p_var.get()
        age = age_var.get()
        sex = sex_var.get()
        hob = hob_var.get()
        cls = cob.get()
        print(f"{name} {pwd} {age} {sex} {hob} {cls}")
        conn = connects()
        cursor = conn.cursor()
        sql = f"insert into user_info values(%s,%s,%s,%s,%s,%s)"
        cursor.execute(sql,(name,pwd,age,sex,hob,cls))
        conn.commit()
        row = cursor.rowcount
        showinfo("成功", message="成功插入" + str(row) + "行")
    except:
        showerror("失败",message="失败")


def clears():
    u_var.set("")
    p_var.set("")
    age_var.set("")
    sex_var.set("女")
    hob_var.set("")
    cob.current(0)


win = Tk()
win.title("模拟sql插入数据")
win.geometry("500x500+700+200")
# 设置标签
Label(win, text="账号:", font=("宋体", 18)).grid(row=0, column=0)
Label(win, text="密码:", font=("宋体", 18)).grid(row=1, column=0)
Label(win, text="年龄:", font=("宋体", 18)).grid(row=2, column=0)
Label(win, text="性别:", font=("宋体", 18)).grid(row=3, column=0)
Label(win, text="爱好:", font=("宋体", 18)).grid(row=4, column=0)
Label(win, text="专业:", font=("宋体", 18)).grid(row=5, column=0)
# 设置文本框
u_var = StringVar()  # 输入账号
Entry(win, font=("宋体", 18), textvariable=u_var).grid(row=0, column=1)
p_var = StringVar()
Entry(win, font=("宋体", 18), textvariable=p_var).grid(row=1, column=1)
age_var = IntVar()
Entry(win, font=("宋体", 18), textvariable=age_var).grid(row=2, column=1)
# 设置单选框
sex_var = StringVar()
sex_var.set("女")  # 设置默认选中
Radiobutton(win, text="男", value="男", font=("宋体", 18), variable=sex_var).place(x=70, y=90)
Radiobutton(win, text="女", value="女", font=("宋体", 18), variable=sex_var).place(x=130, y=90)
hob_var = StringVar()
Entry(win, font=("宋体", 18), textvariable=hob_var).grid(row=4, column=1)
# 设置下拉框
cob = ttk.Combobox(win, font=("宋体", 18), state='readonly')
cob['values'] = values()
cob.current(0)  # 默认选中第一个
cob.place(x=66, y=150)
# 设置按钮
Button(win, text="提交", command=commits, font=("宋体", 18)).place(x=66, y=190)
Button(win, text="退出", command=exits, font=("宋体", 18)).place(x=135, y=190)
Button(win, text="清空", command=clears, font=("宋体", 18)).place(x=205, y=190)
win.mainloop()
